import asyncio
import datetime
import time
import hashlib
from itertools import chain
import aiohttp as aiohttp
import pymysql
import requests
import json


# 通过MD5获取签名
def get_sign(p_ak, p_secret, p_millis):
    arg = p_ak + p_secret + p_millis
    res_sign = hashlib.md5(arg.encode('utf-8')).hexdigest()
    return res_sign


# 获取请求密钥
def get_requestSecret(p_response):
    dict_str = json.loads(p_response)  # 转换成json格式
    dic_data = dict_str["datas"]
    return dic_data["requestSecret"]


async def main(pool,list_param):#启动
    sem = asyncio.Semaphore(pool)
    async with aiohttp.ClientSession() as session:#给所有的请求，创建同一个session
        tasks = []
        [tasks.append(control_sem(sem, 'http://10.68.138.194/gateway/api/001008002016003/dataSharing/U8tmD1UJe22fMeO3.htm?{}'.format(lic), session)) for lic in list_param]
        await asyncio.wait(tasks)


async def control_sem(sem, url, session):  # 限制信号量
    async with sem:
        await fetch(url, session)
# 保存访问接口返回的json数据
li = []


async def fetch(url, session):  # 开启异步请求
    async with session.get(url) as resp:
        json = await resp.json()
        li.append(json)


def deal_lb(data):

    ak = '3a91b44c07a744bda09c4420fab0994e'
    secret = 'fe77dc8de20f4250b63968f10a87d3c2'
    url = "http://10.68.138.194/gateway/app/refreshTokenByKey.htm"
    # 获取数据的地址
    api_url = "http://10.68.138.194/gateway/api/001008002016003/dataSharing/U8tmD1UJe22fMeO3.htm"
    # 获取当前时间戳
    millis = int(round(time.time() * 1000))
    # 获取sign
    sign = get_sign(ak, secret, str(millis))
    # 获取刷新密钥
    params = {"appKey": ak, "sign": sign, "requestTime": str(millis)}
    res = requests.get(url=url, params=params)
    # 获取请求密钥
    refreshSecret = get_requestSecret(res.text)
    refreshSign = get_sign(ak, refreshSecret, str(millis))
    list_params = []
    for da in data:
        das = 'cardId='+da.replace(',','')+'&appKey=3a91b44c07a744bda09c4420fab0994e&'+'sign='+refreshSign+'&requestTime='+str(millis)
        list_params.append(das)
    return list_params


# 从跳板机中的数据库中将数据查询出来
def mysql2local():
    db = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='1234', db='test')
    cursor = db.cursor()
    res_sql = "select sfzjhm from yzsfz limit 1,150;"
    cursor.execute(res_sql)
    result = cursor.fetchall()
    db.close()
    return result


# 将结果集插入数据库
def datas2mysql(p_datas):
    db = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='1234', db='test')
    cursor = db.cursor()
    res_sql = "insert into yzhj(`name`,`relation`,`useToName`,`sex`,`birthplace`,`nation`,`country`,`membership`" \
              ",`dateOfBirth`,`cityOtherAddress`,`height`,`bloodType`,`culturalLevel`,`maritalStatus`,`veteranStatus`" \
              ",`serviceSpaces`,`whenToLocal`,`dueToLocal`,`whatCountryToLocal`,`provincialCitiesAndCountiesToLocal`" \
              ",`whereToLocal`,`whenToLocalCity`,`dueToLocalCity`,`whereToLocalCityCountry`,`whereToLocalArea`" \
              ",`whereToLocalCity`,`contractor`,`registrationDate`,`numbering`,`accountType`,`religion`" \
              ",`registrationAuthority`,`householder`,`householderIDCard`,`idcard`) " \
              "values(%(name)s,%(relation)s,%(useToName)s,%(sex)s,%(birthplace)s,%(nation)s,%(country)s" \
              ",%(membership)s" \
              ",%(dateOfBirth)s,%(cityOtherAddress)s,%(height)s,%(bloodType)s,%(culturalLevel)s,%(maritalStatus)s" \
              ",%(veteranStatus)s,%(serviceSpaces)s,%(whenToLocal)s,%(dueToLocal)s,%(whatCountryToLocal)s" \
              ",%(provincialCitiesAndCountiesToLocal)s,%(whereToLocal)s,%(whenToLocalCity)s,%(dueToLocalCity)s" \
              ",%(whereToLocalCityCountry)s,%(whereToLocalArea)s,%(whereToLocalCity)s,%(contractor)s" \
              ",%(registrationDate)s,%(numbering)s,%(accountType)s,%(religion)s,%(registrationAuthority)s" \
              ",%(householder)s,%(householderIDCard)s,%(idcard)s)"
    cursor.executemany(res_sql, p_datas)
    db.commit()


# 定义一个切割数组的方法
def split_arr(arr,size):
    array_sj = []
    for i in range(0,len(arr)+1,size):
        arr_i = arr[i:i+size]
        if arr_i != []:
            array_sj.append(arr_i)
    return array_sj


if __name__ == '__main__':
    print(datetime.datetime.now())
    # 用于保存mysql查询出来的数据，是一个二维元祖
    result = mysql2local()
    # 定义一个保存二维元祖里数据的列表
    lb = []
    # 二维元祖转为二维列表
    for i in result:
        lb.append(list(i))
    # 二维列表转一维列表
    data = list(chain(*lb))
    # 将data列表切割成若干个二维列表，按照100的大小来切割
    array_qp = split_arr(data,100)
    # 对切割后的数组进行循环遍历，将结果集保存至li(返回的json结果集)中
    for arr in array_qp:
        list_param = deal_lb(arr)
        loop = asyncio.get_event_loop()
        loop.run_until_complete(main(pool=10, list_param=list_param))
    # 保存li中的非空数据
    list_dic_datas = []
    for da in li:
        # 判断li(返回的json结果集)中的数据是否访问成功，并且
        if da["code"] == '00' and len(da["datas"]) > 0:
            dic_datas = da["datas"][0]
            print(dic_datas)
            list_dic_datas.append(dic_datas)
    print(len(list_dic_datas))
    datas2mysql(list_dic_datas)
    print('SUCCESS!')
    print(datetime.datetime.now())
